<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace='Calendar' xmlns='http://ibatis.apache.org/mapping' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
  <statements>
    <select id='FindCalendarList' parameterClass='string' resultClass='Calendar'>
      SELECT CL.[YEAR] as Year, CL.WORKCCODE as WorkCCode,WC.WORKCNAME as WorkCName,YearDayTotal=COUNT(1)
      ,WorkDayTotal=SUM(CASE WHEN CL.CTYPE=1 THEN 1 ELSE 0 END)
      ,WeekDayTotal=SUM(CASE WHEN CL.CTYPE=2 THEN 1 ELSE 0 END)
      ,HolidayTotal=SUM(CASE WHEN CL.CTYPE=3 THEN 1 ELSE 0 END)
      ,DaysOffTotal=SUM(CASE WHEN CL.CTYPE=4 THEN 1 ELSE 0 END)
      FROM CALENDAR CL
      INNER JOIN WORKCENTER WC ON CL.WORKCCODE=WC.WORKCCODE
      WHERE $value$
      GROUP BY CL.[YEAR], CL.WORKCCODE,WC.WORKCNAME
    </select>

    <insert id="CopyInsert" parameterClass='Calendar'>
      INSERT INTO Calendar([Id], [Year],[WorkCCode],[CDate],[Ctype], [Creator], [CreateDate],[CreatorCode])
      select [Id]=NEWID(), [Year], WorkCCode = #TargetWorkCCode#, CDate, Ctype, Creator = #Creator#, CreateDate = GETDATE(), CreatorCode = #CreatorCode#
      from [Calendar]
      where WorkCCode = #WorkCCode# and [Year] = #Year#
    </insert>
  </statements>
</sqlMap>
